/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.aptech.dao;

import com.aptech.utility.DBType;
import com.aptech.utility.DBUtil;
import com.aptech.beans.Users;
import java.sql.*;

/**
 *
 * @author Khoa
 */
public class UserManager {

    public static ResultSet displayAllRows() throws SQLException {
        String sql = "SELECT Users.UserID, Users.UserName, Users.Password, Users.Email, Users.Address, Users.DateOfBirth, Users.CreateDate, Roles.RoleName"
                + " FROM Users"
                + " INNER JOIN UserInRoles"
                + " ON Users.UserID=UserInRoles.UserID"
                + " INNER JOIN Roles"
                + " ON UserInRoles.RoleID=Roles.RoleID";
        Connection conn = DBUtil.getConnection(DBType.myConnection);

        PreparedStatement preparedStatement = conn.prepareStatement(sql,
                ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
        ResultSet resultSet = preparedStatement.executeQuery();
        return resultSet;
        
    }

    public static Users getRow(String UserName, String Password) throws SQLException {

        String sql = "SELECT UserID, UserName, Password, Email, Address, DateOfBirth, CreateDate FROM Users WHERE UserName = ? AND Password = ?";

        Connection conn = DBUtil.getConnection(DBType.myConnection);
        PreparedStatement preparedStatement = conn.prepareStatement(sql,
                ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);

        preparedStatement.setString(1, UserName);
        preparedStatement.setString(2, Password);

        ResultSet resultSet = preparedStatement.executeQuery();

        if (resultSet.next()) {
            Users user = new Users();
            user.setUserName(UserName);
            user.setPassword(Password);

            return user;
        } else {
            return null;
        }

    }

    public static Users checkUsername(String UserName) throws SQLException {

        String sql = "SELECT * FROM Users WHERE UserName = ?";

        Connection conn = DBUtil.getConnection(DBType.myConnection);
        PreparedStatement preparedStatement = conn.prepareStatement(sql,
                ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);

        preparedStatement.setString(1, UserName);

        ResultSet resultSet = preparedStatement.executeQuery();

        if (resultSet.next()) {
            Users user = new Users();
            user.setUserName(UserName);
            return user;
        } else {
            return null;
        }

    }

    public static boolean insert(Users user) throws SQLException {

        String sql = "INSERT INTO Users (UserName, Password, Email, Address, DateOfBirth, CreateDate) VALUES (?, ?, ?, ?, ?, ?)";

        Connection conn = DBUtil.getConnection(DBType.myConnection);
        conn.setAutoCommit(false);

        PreparedStatement pstm_Insert1 = conn.prepareStatement(sql,
                ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
        System.out.println(user.getUserID());
        pstm_Insert1.setString(1, user.getUserName());
        pstm_Insert1.setString(2, user.getPassword());
        pstm_Insert1.setString(3, user.getEmail());
        pstm_Insert1.setString(4, user.getAddress());
        pstm_Insert1.setTimestamp(5, user.getDateOfBirth());
        pstm_Insert1.setTimestamp(6, user.getCreateDate());
        int affected = pstm_Insert1.executeUpdate();

        if (affected != 0) {
            String sqlSelect = "SELECT UserID FROM Users WHERE UserName=?";
            PreparedStatement pstm_Select = conn.prepareStatement(sqlSelect,
                    ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            pstm_Select.setString(1, user.getUserName());
            ResultSet resultSet = pstm_Select.executeQuery();

            if (resultSet.next()) {
                String sqlInsert = "INSERT INTO UserInRoles (UserID, RoleID) VALUES (?, ?)";
                PreparedStatement pstm_Insert2 = conn.prepareStatement(sqlInsert,
                        ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);

                pstm_Insert2.setInt(1, resultSet.getInt(1));
                pstm_Insert2.setInt(2, user.getRoleID());
                pstm_Insert2.executeUpdate();
                conn.commit();
            }

        } else if (conn != null) {
            conn.rollback();
        }

//        conn.commit(); //transaction block end
        return affected == 1;
    }

    public static boolean update(Users user) throws SQLException {

        String sql_Update1 = "UPDATE Users"
                + " SET Users.Email=?, Users.Address=?, Users.DateOfBirth=?"
                + " FROM Users, UserInRoles"
                + " WHERE Users.UserID = UserInRoles.UserID"
                + " AND Users.UserID=?";

        String sql_Update2 = "UPDATE UserInRoles"
                + " SET UserInRoles.RoleID=?"
                + " FROM Users, UserInRoles"
                + " WHERE Users.UserID=?"
                + " AND Users.UserID = UserInRoles.UserID";
        String sqlSelect = "SELECT UserID FROM Users WHERE UserName=?";

        Connection conn = DBUtil.getConnection(DBType.myConnection);
        conn.setAutoCommit(false);

        int affected2 = 0;
        int affected = 0;

        PreparedStatement pstm_Select = conn.prepareStatement(sqlSelect,
                ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        pstm_Select.setString(1, user.getUserName());
        ResultSet resultSet = pstm_Select.executeQuery();
        if (resultSet.next()) {
            PreparedStatement pstm_Update1 = conn.prepareStatement(sql_Update1,
                    ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);

            pstm_Update1.setString(1, user.getEmail());
            pstm_Update1.setString(2, user.getAddress());
            pstm_Update1.setTimestamp(3, user.getDateOfBirth());
            pstm_Update1.setInt(4, resultSet.getInt(1));
            affected = pstm_Update1.executeUpdate();

            if (affected == 1) {
                PreparedStatement pstm_Update2 = conn.prepareStatement(sql_Update2,
                        ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
                pstm_Update2.setInt(1, user.getRoleID());
                pstm_Update2.setInt(2, resultSet.getInt(1));

                affected2 = pstm_Update2.executeUpdate();
                conn.commit();
            }
        }
        return affected == 1 && affected2 == 1;
    }

    public static Users checkUserInRole(String UserName) throws SQLException {

        String sql = "SELECT UserInRoles.RoleID"
                + " FROM UserInRoles"
                + " INNER JOIN Users"
                + " ON UserInRoles.UserID=Users.UserID"
                + " WHERE Users.UserName=?";

        Connection conn = DBUtil.getConnection(DBType.myConnection);
        PreparedStatement preparedStatement = conn.prepareStatement(sql,
                ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);

        preparedStatement.setString(1, UserName);

        ResultSet resultSet = preparedStatement.executeQuery();

        if (resultSet.next()) {
            Users user = new Users();
            user.setRoleID(resultSet.getInt(1));
            return user;
        } else {
            return null;
        }
    }

    public static Users checkRoleByRoleName(String RoleName) throws SQLException {

        String sql = "SELECT RoleID FROM Roles WHERE RoleName=?";

        Connection conn = DBUtil.getConnection(DBType.myConnection);
        PreparedStatement preparedStatement = conn.prepareStatement(sql,
                ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);

        preparedStatement.setString(1, RoleName);

        ResultSet resultSet = preparedStatement.executeQuery();

        if (resultSet.next()) {
            Users user = new Users();
            user.setRoleID(resultSet.getInt(1));
            return user;
        } else {
            return null;
        }
    }

    public static ResultSet getRoles() throws SQLException {
        String sql = "SELECT * FROM Roles";
        Connection conn = DBUtil.getConnection(DBType.myConnection);
        PreparedStatement preparedStatement = conn.prepareStatement(sql,
                ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);

        ResultSet resultSet = preparedStatement.executeQuery();

        return resultSet;
    }
    
    public static Users getUserID(String UserName) throws SQLException {
        String sql = "SELECT UserID FROM Users WHERE UserName=?";
        Connection conn = DBUtil.getConnection(DBType.myConnection);
        PreparedStatement preparedStatement = conn.prepareStatement(sql,
                ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);

        preparedStatement.setString(1, UserName);

        ResultSet resultSet = preparedStatement.executeQuery();

        if (resultSet.next()) {
            Users user = new Users();
            user.setUserName(UserName);
            user.setUserID(resultSet.getInt(1));
            return user;
        } else {
            return null;
        }
    }
    
    public static Boolean changePassword(String NewPassword, String UserName, String OldPassword) throws SQLException {

        String sql = "UPDATE Users SET Password=? FROM Users WHERE UserName=? AND Password=?";

        Connection conn = DBUtil.getConnection(DBType.myConnection);
        PreparedStatement preparedStatement = conn.prepareStatement(sql,
                ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        
        preparedStatement.setString(1, NewPassword);
        preparedStatement.setString(2, UserName);
        preparedStatement.setString(3, OldPassword);
        
        int affected = preparedStatement.executeUpdate();
        
        return affected == 1;
        
    }
    
    public static Boolean changePassword(String NewPassword, String UserName) throws SQLException {

        String sql = "UPDATE Users SET Password=? FROM Users WHERE UserName=?";

        Connection conn = DBUtil.getConnection(DBType.myConnection);
        PreparedStatement preparedStatement = conn.prepareStatement(sql,
                ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        
        preparedStatement.setString(1, NewPassword);
        preparedStatement.setString(2, UserName);
        
        int affected = preparedStatement.executeUpdate();
        
        return affected == 1;
        
    }
}
